set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;


with dwd_tms_shipment_stop_base_dt as (
    select
         ship.shipment_no
        ,ship.shipment_name
        ,ship.ship_attr                --业务属性1:正班2:加班
        ,ship.table_type               --1-干线；2-支线
        ,stop.network_code             --网点名称
        ,stop.network_name             --网点名称
        ,stop.planned_arrival_time     --计划到车时间
        ,stop.actual_arrival_time      --实际到车时间
        ,stop.planned_departure_time   --计划发车时间
        ,stop.actual_departure_time    --实际发车时间
        ,stop.lock_time                --锁车时间
        ,stop.unlock_time              --解锁时间
        ,stop.next_network_code        --下一站点网点编码
        ,stop.next_network_name        --下一站点网点名称
        ,size(split(ship.shipment_name,'-')) as shipment_nodes
    from (
        select
             shipment_no
            ,shipment_name
            ,business_attribute as ship_attr  --业务属性1:正班2:加班
            ,table_type --1-干线；2-支线
            ,dt
        from jms_dwd.dwd_tmsnew_shipment_union_base_dt t1 --支干线任务表
        where dt between date_sub('{{ execution_date | cst_ds }}', 50) and date_add('{{ execution_date | cst_ds }}',7)
    ) ship
    left join (
        select
             base.shipment_no
            ,base.network_code             --网点编码
            ,base.network_name             --网点名称
            ,base.planned_arrival_time     --计划到车时间
            ,base.actual_arrival_time      --实际到车时间
            ,base.planned_departure_time   --计划发车时间
            ,base.actual_departure_time    --实际发车时间
            ,base.lock_time                --锁车时间
            ,base.unlock_time              --解锁时间
            ,next.network_code as next_network_code  --下一站点网点编码
            ,next.network_name as next_network_name  --下一站点网点名称
        from (
            select
                 shipment_no
                ,network_code             --网点名称
                ,network_name             --网点名称
                ,planned_arrival_time     --计划到车时间
                ,actual_arrival_time      --实际到车时间
                ,planned_departure_time   --计划发车时间
                ,actual_departure_time    --实际发车时间
                ,lock_time                --锁车时间
                ,unlock_time              --解锁时间
                ,sort_num
            from jms_dwd.dwd_tmsnew_shipment_stop_union_base_dt
            where dt between date_sub('{{ execution_date | cst_ds }}', 50) and date_add('{{ execution_date | cst_ds }}',7)
        ) base
        left join (
            select
                 shipment_no
                ,network_code             --网点名称
                ,network_name             --网点名称
                ,planned_arrival_time     --计划到车时间
                ,actual_arrival_time      --实际到车时间
                ,planned_departure_time   --计划发车时间
                ,actual_departure_time    --实际发车时间
                ,lock_time                --锁车时间
                ,unlock_time              --解锁时间
                ,sort_num
            from jms_dwd.dwd_tmsnew_shipment_stop_union_base_dt
            where dt between date_sub('{{ execution_date | cst_ds }}', 50) and date_add('{{ execution_date | cst_ds }}',7)
        ) next on base.shipment_no = next.shipment_no
              and base.sort_num = next.sort_num -1 
    ) stop on ship.shipment_no = stop.shipment_no
),

dm_waybill_step_reach_details_dt as (
    select
         reach.waybill_no
        ,step.scan_site_no                        --节点顺序
        ,step.scan_site_code                      --当前站点编码
        ,step.scan_site_name                      --当前站点名称
        ,step.scan_site_center_no                 --当前站点中心编号
        ,step.deliver_time                        --目的网点实际出仓时间
        ,reach.last_network_code                  --目的网点编码
        ,reach.last_network_name                  --目的网点名称
        ,step.first_code                          --一段码
        ,step.second_code                         --二段码
        ,step.actual_sign_time_tmp                --实际签收时间
        ,step.aging_sign_time_tmp                 --实效签收时间
        ,step.scan_site_arrival_time              --到件时间
        ,step.scan_site_send_time                 --发件时间
        ,step.scan_site_actual_shipment_no        --发件任务号
        ,step.scan_site_actual_shipment_name      --发件任务号名称
        ,stop_send.ship_attr                     as scan_site_ship_attr                 --发件任务号业务属性1:正班2:加班
        ,stop_send.table_type                    as scan_site_ship_table_type           --发件任务号任务类型1-干线；2-支线
        ,stop_send.next_network_code             as scan_site_ship_next_network_code    --发件任务号任务下一站点编码
        ,stop_send.shipment_nodes                as scan_site_ship_nodes                --发件任务号任务单节点数
        ,stop_send.lock_time                     as scan_site_seal_time                 --发件任务单实际封车时间
        ,stop_send.actual_departure_time         as scan_site_actual_send_time          --发件任务号实际发车时间
        ,stop_send.planned_departure_time        as scan_site_planned_departure_time    --发件任务号规划发车时间
        ,stop_pre_send.actual_arrival_time       as scan_site_actual_arrival_time       --上一站发件任务号实际到达时间
        ,stop_pre_send.planned_arrival_time      as scan_site_planned_arrival_time      --上一站发件任务号规划到达时间
        ,step.scan_site_arrival_bagg_code         --到件包号
        ,step.scan_site_arrival_bagg_name         --到件包名
        ,step.scan_site_arrival_bagg_addr_code    --到件包建包目的地编码
        ,step.scan_site_arrival_bagg_addr_name    --到件包建包目的地名称
        ,step.scan_site_send_bagg_code            --发件包号
        ,step.scan_site_send_bagg_name            --发件包名
        ,step.scan_site_send_bagg_addr_code       --发件包建包目的地编码
        ,step.scan_site_send_bagg_addr_name       --发件包建包目的地名称
        ,step.scan_site_if_stop                   --发件是否经停
        ,reach.ziyou_platform_effect              --自有平台时效标准（区县时效）
        ,reach.if_platform_standard               --自有平台是否达成
        ,reach.cainiao_platform_name              --七星潭平台名称
        ,reach.cainiao_platform_effect            --七星潭平台时效标准
        ,reach.if_cainiao_platform_pres_standard  --七星潭平台时效是否达成:1达成,0未达成
        ,reach.start_city_code                    --始发城市编码
        ,reach.start_city_name                    --始发城市名称
        ,reach.sign_city_code                     --签收城市编码
        ,reach.sign_city_name                     --签收城市名称
        ,reach.taking_time                        --揽收时间
        ,reach.taking_date                        --揽收日期
        ,reach.first_network_code                 --始发网点编码
        ,reach.first_network_name                 --始发网点名称
        ,reach.plan_sign_date                     --规划签收时间
        ,reach.economic_circle_name               --经济圈名称
        ,reach.if_reback                          --是否退回件:1是,0否
        ,reach.if_transfer                        --是否转寄件:1是,0否
        ,reach.if_intercept                       --是否拦截件:1是,0否
        ,reach.actual_effect                      --实际时效  20230216新增
        ,reach.if_taitc                           --问题件抬头错标识  20230330新增
        ,reach.order_source_code                  --订单来源编码 20230330新增
        ,reach.order_source_name                  --订单来源名称 20230330新增
        ,reach.if_reback_reach                    --退转件是否达成 20230330新增
        ,reach.reback_apply_time                  --退转件申请时间
        ,reach.intercept_registration_time        --拦截件登记时间
        ,reach.cainiao_plan_sign_time             --达成率菜鸟规划签收时间
        ,reach.ziyou_plan_sign_time               --达成率自由规划签收时间
        ,reach.start_center_code                  --始发中心编码
        ,reach.start_center_name                  --始发中心名称
        ,reach.end_center_code                    --目的中心编码
        ,reach.end_center_name                    --目的中心名称
        ,step.scan_site_if_bagg                   --当前站点是否建包扫描
        ,step.next_site_code_tmp
        ,step.actual_sign_code
        ,step.scan_site_arrival_shipment_no       --到件任务号
        ,reach.customer_code                      --客户编码
        ,reach.customer_name                      --客户名称
        ,bagg.scan_site_bagg_user_code            --当前站点建包扫描员编号
        ,bagg.scan_site_bagg_user_name            --当前站点建包扫描员名称
        ,reach.extra_time                         --特殊乡镇时间 20230920新增
        ,stop_arr.ship_attr               as scan_site_arrival_ship_attr               --到件任务号业务属性1:正班2:加班
        ,stop_arr.table_type              as scan_site_arrival_ship_table_type         --到件任务号任务类型1-干线；2-支线
        ,cast(stop_arr.planned_arrival_time as string) as scan_site_arrival_planned_arrival_time    --到件任务号计划到车时间
        ,cast(stop_arr.actual_arrival_time  as string) as scan_site_arrival_actual_arrival_time     --到件任务号实际到车时间
        ,step.pre_site_send_shipment_no   as pre_site_send_shipment_no                 --上一站发件任务号
        ,stop_pre_send.ship_attr          as pre_site_send_ship_attr                   --上一站发件任务号业务属性1:正班2:加班
        ,stop_pre_send.table_type         as pre_site_send_ship_table_type             --上一站发件任务号任务类型1-干线；2-支线
        ,reach.if_trans_mail              as if_trans_mail      --是否转邮件
        ,step.scan_site_min_time          as scan_site_min_time --每个站点最早的扫描时间
    from (
        select
             reach.waybill_no                         as waybill_no                         --运单号
            ,reach.districtprescription               as ziyou_platform_effect              --自有平台时效标准（区县时效）
            ,reach.is_pres_standard                   as if_platform_standard               --自有平台是否达成
            ,reach.cainiao_platform_name              as cainiao_platform_name              --七星潭平台名称
            ,reach.cainiao_platform_effect            as cainiao_platform_effect            --七星潭平台时效标准
            ,reach.cainiao_is_platform_pres_standard  as if_cainiao_platform_pres_standard  --七星潭平台时效是否达成:1达成,0未达成
            ,reach.start_city_id                      as start_city_code                    --始发城市编码
            ,reach.start_city_desc                    as start_city_name                    --始发城市名称
            ,reach.sign_city_id                       as sign_city_code                     --签收城市编码
            ,reach.sign_city_desc                     as sign_city_name                     --签收城市名称
            ,reach.taking_time                        as taking_time                        --揽收时间
            ,to_date(reach.taking_time)               as taking_date                        --揽收日期
            ,reach.start_pick_network_code            as first_network_code                 --始发网点编码
            ,reach.start_pick_network_name            as first_network_name                 --始发网点名称
            ,reach.sign_network_code                  as last_network_code                  --签收网点编码
            ,reach.sign_network_name                  as last_network_name                  --签收网点名称
            ,reach.plan_sign_date                     as plan_sign_date                     --规划签收时间
            ,case when reach.economic_circle_type = 1 then '珠三角'
                  when reach.economic_circle_type = 2 then '长三角'
                  when reach.economic_circle_type = 3 then '京津冀'
                  end as economic_circle_name      --经济圈名称
            ,abnormal.if_reback                       as if_reback                     --是否退回件:1是,0否
            ,abnormal.if_transfer                     as if_transfer                   --是否转寄件:1是,0否
            ,abnormal.if_intercept                    as if_intercept                  --是否拦截件:1是,0否
            ,abs(datediff(reach.pres_sign_time,reach.taking_time)) as actual_effect    --实际时效  20230216新增
            ,abnormal.if_taitc                        as if_taitc                      --问题件抬头错
            ,reach.island_code                        as order_source_code             --订单来源编码
            ,reach.island_name                        as order_source_name             --订单来源名称
            ,case when to_date(abnormal.reback_apply_time) <= to_date(reach.plan_sign_date)
                  or to_date(abnormal.intercept_registration_time) <= to_date(reach.plan_sign_date)
                  then 1 else 0 end as if_reback_reach --退转拦截最早时间当做时效签收时间,判断自有平台是否达成
            ,nvl(abnormal.reback_apply_time          ,'2099-00-00 00:00:00') as reback_apply_time             --退转件申请时间
            ,nvl(abnormal.intercept_registration_time,'2099-00-00 00:00:00') as intercept_registration_time   --拦截件登记时间
            ,reach.plan_cainiao_platform_date   as cainiao_plan_sign_time  --达成率菜鸟规划签收时间
            ,reach.plan_sign_date               as ziyou_plan_sign_time    --达成率自有规划签收时间
            ,reach.start_center_code            as start_center_code       --始发中心编码
            ,reach.start_center_name            as start_center_name       --始发中心名称
            ,reach.sign_center_code             as end_center_code         --目的中心编码
            ,reach.sign_center_name             as end_center_name         --目的中心名称
            ,reach.customer_code                as customer_code           --客户编码
            ,reach.customer_name                as customer_name           --客户名称
            ,reach.extra_time                   as extra_time              --特殊乡镇时间
            ,case when trans.waybill_no is not null then 1 else 0 end as if_trans_mail --是否转邮件
        from jms_dm.dm_prescription_reach_details_dt reach --达成率明细
        left join (
            select
                  waybill_no
                 ,max(case when scan_type = 'reback' and type_code = 4 then 1 else 0 end) as if_reback   --是否退回件:1是,0否
                 ,max(case when scan_type = 'reback' and type_code = 5 then 1 else 0 end) as if_transfer --是否转寄件:1是,0否
                 ,max(case when scan_type = 'reback' then scan_time end) as reback_apply_time            --退转件申请时间
                 ,max(case when scan_type = 'intercept' then 1 else 0 end) as if_intercept               --是否拦截件，1：是，0：否
                 ,max(case when scan_type = 'intercept' then intercept_registration_time end) as intercept_registration_time   --拦截件登记时间
                 ,max(if_taitc) as if_taitc --问题件抬头错类型
            from (
                select
                     *
                    ,max(case when scan_type = 'difficult' and type_desc like '%抬头错%' then 1 else 0 end) over(partition by waybill_no) as if_taitc --问题件问题类型
                    ,row_number() over (partition by waybill_no,scan_type order by scan_time asc) as rn
                from jms_dwd.dwd_wide_abnormal_union_waybill_dt
                where dt between date_sub('{{ execution_date | cst_ds }}',45) and '{{ execution_date | cst_ds }}'
            ) a where a.rn = 1
            group by waybill_no
        ) abnormal on reach.waybill_no = abnormal.waybill_no
        left join (
            select
                bill_code as waybill_no
            from jms_dwd.dwd_tab_barscan_transfer_mail_base_dt
            where dt between date_sub('{{ execution_date | cst_ds }}',26) and '{{ execution_date | cst_ds }}'
            group by bill_code
        ) trans on trans.waybill_no = reach.waybill_no
        where reach.dt between date_sub('{{ execution_date | cst_ds }}',26) and '{{ execution_date | cst_ds }}'
          and to_date(reach.plan_cainiao_platform_date) between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
          and (reach.cainiao_is_platform_pres_standard = 0 or reach.is_pres_standard = 0)
          --签收类型为时效签收,且自由平台或七星潭未达成票数
    ) reach
    left join (
        select
             waybill_no
            ,send_station_number                                     as scan_site_no
            ,send_station_network_code                               as scan_site_code       --当前站点编码
            ,send_station_network_name                               as scan_site_name       --当前站点名称
            ,send_center_station_number                              as scan_site_center_no  --当前站点中心编号
            ,deliver_early_scantime                                  as deliver_time         --目的网点实际出仓时间
            ,first_code                                              as first_code           --一段码
            ,second_code                                             as second_code          --二段码
            ,send_station_arrival_early_time                         as scan_site_arrival_time              --到件时间
            ,send_station_send_later_time                            as scan_site_send_time                 --发件时间
            ,send_station_shipment_no                                as scan_site_actual_shipment_no        --发件任务号
            ,send_station_loading_send_later_shipment_name           as scan_site_actual_shipment_name      --发件任务号名称
           --,send_station_lock_time                                  as scan_site_seal_time                 --发件任务单实际封车时间
           --,send_station_actual_departure_later_time                as scan_site_actual_send_time          --发件任务号实际发车时间
           --,send_station_planned_departure_time                     as scan_site_planned_departure_time    --发件任务号规划发车时间
            ,send_station_unloading_arrival_early_shipment_no        as scan_site_arrival_shipment_no       --到件任务号
           --,send_station_unloading_arrival_actual_arrival_time      as scan_site_actual_arrival_time       --到件任务号实际到达时间
           --,send_station_unloading_arrival_planned_arrival_time     as scan_site_planned_arrival_time      --到件任务号规划到达时间
            ,sent_station_arrival_packagecode                        as scan_site_arrival_bagg_code         --到件包号
            ,send_station_arrival_packagecode_onbagging_center_name  as scan_site_arrival_bagg_name         --到件包名
            ,send_station_arrival_packagecode_unbagging_center_code  as scan_site_arrival_bagg_addr_code    --到件包建包目的地编码
            ,send_station_arrival_packagecode_unbagging_center_name  as scan_site_arrival_bagg_addr_name    --到件包建包目的地名称
            ,sent_station_send_packagecode                           as scan_site_send_bagg_code            --发件包号
            ,send_station_send_packagecode_onbagging_center_name     as scan_site_send_bagg_name            --发件包名
            ,send_station_send_packagecode_unbagging_center_code     as scan_site_send_bagg_addr_code       --发件包建包目的地编码
            ,send_station_send_packagecode_unbagging_center_name     as scan_site_send_bagg_addr_name       --发件包建包目的地名称
            ,0                                                       as scan_site_if_stop
            ,case when send_station_bagging_scantim is not null then 1 else 0 end as scan_site_if_bagg
            ,send_station_next_station                               as next_site_code_tmp
            ,least(nvl(outstore_early_scantime,'2099-12-31 00:00:00'),nvl(sign_early_scantime    ,'2099-12-31 00:00:00')) as actual_sign_time_tmp   --实际签收时间
            ,least(nvl(sign_early_scantime    ,'2099-12-31 00:00:00'),nvl(outstore_early_scantime,'2099-12-31 00:00:00'),nvl(instore_early_scantime,'2099-12-31 00:00:00')) as aging_sign_time_tmp --实效签收时间
            ,coalesce(instore_early_scansitecode,outstore_early_scansitecode,sign_early_scansitecode) as actual_sign_code --20231008新增
            ,lag(send_station_shipment_no) over(partition by waybill_no order by send_station_number) as pre_site_send_shipment_no --上一站发件任务号 20231008新增
            ,send_station_early_scan_time as scan_site_min_time --每个站点最早的扫描时间
        from jms_dwd.dwd_wide_unsign_trace_waybill_step_dt
        where dt between date_sub('{{ execution_date | cst_ds }}',26) and '{{ execution_date | cst_ds }}'
        and send_station_network_code is not null --剔除出库入库没网点的轨迹
    ) step on reach.waybill_no = step.waybill_no
    left join (
        select *
        from (
            select
                  waybill_no
                 ,scan_site_code
                 ,scan_user_code as scan_site_bagg_user_code
                 ,scan_user      as scan_site_bagg_user_name
                 ,scan_time
                 ,row_number() over(partition by waybill_no,scan_site_code order by scan_time) as rn
            from jms_dwd.dwd_tab_barscan_bagging_base_dt --建包扫描
            where dt between date_sub('{{ execution_date | cst_ds }}',26) and '{{ execution_date | cst_ds }}'
        ) a where a.rn = 1
    ) bagg on step.waybill_no = bagg.waybill_no
          and step.scan_site_code = bagg.scan_site_code
    left join dwd_tms_shipment_stop_base_dt as stop_send
       on stop_send.shipment_no = step.scan_site_actual_shipment_no --发车任务单
      and stop_send.network_code = step.scan_site_code
    left join dwd_tms_shipment_stop_base_dt as stop_pre_send
       on stop_pre_send.shipment_no = step.pre_site_send_shipment_no --上一站发车任务单
      and stop_pre_send.network_code = step.scan_site_code
    left join dwd_tms_shipment_stop_base_dt as stop_arr
       on stop_arr.shipment_no = step.scan_site_arrival_shipment_no --到车任务单
      and stop_arr.network_code = step.scan_site_code
)




insert overwrite table jms_dm.dm_whole_decide_duty_detail_dt
select
     waybill_no                            as waybill_no                           --运单号
    ,ziyou_platform_effect                 as ziyou_platform_effect                --自由平台时效标准
    ,if_platform_standard                  as if_platform_standard                 --自有平台时效是否达成
    ,cainiao_platform_name                 as cainiao_platform_name                --七星潭平台名称
    ,cainiao_platform_effect               as cainiao_platform_effect              --七星潭平台时效标准
    ,if_cainiao_platform_pres_standard     as if_cainiao_platform_pres_standard    --七星潭平台时效是否达成:1达成,0未达成
    ,if_reback                             as if_reback                            --是否退回件:1是,0否
    ,if_transfer                           as if_transfer                          --是否转寄件:1是,0否
    ,if_intercept                          as if_intercept                         --是否拦截件:1是,0否
    ,start_city_code                       as start_city_code                      --始发城市编码
    ,start_city_name                       as start_city_name                      --始发城市名称
    ,sign_city_code                        as sign_city_code                       --签收城市编码
    ,sign_city_name                        as sign_city_name                       --签收城市名称
    ,taking_time                           as taking_time                          --揽收时间
    ,deliver_time                          as deliver_time                         --实际出仓时间
    ,aging_sign_time                       as aging_sign_time                      --时效签收时间
    ,actual_sign_time                      as actual_sign_time                     --实际签收时间
    ,economic_circle_name                  as economic_circle_name                 --经济圈名称
    ,scan_site_no                          as scan_site_no                         --当前站点顺序号
    ,scan_site_code                        as scan_site_code                       --当前站点编码
    ,scan_site_name                        as scan_site_name                       --当前站点名称
    ,scan_site_type                        as scan_site_type                       --当前站点类型
    ,scan_site_arrival_time                as scan_site_arrival_time               --当前站点到件时间
    ,scan_site_send_time                   as scan_site_send_time                  --当前站点发件时间
    ,scan_site_actual_arrival_time         as scan_site_actual_arrival_time        --当前站点实际到车时间
    ,scan_site_actual_send_time            as scan_site_actual_send_time           --当前站点实际发车时间
    ,scan_site_seal_time                   as scan_site_seal_time                  --当前站点封车时间
    ,scan_site_planned_departure_time      as scan_site_planned_departure_time     --当前站任务号规划发车时间
    ,scan_site_planned_arrival_time        as scan_site_planned_arrival_time       --当前站任务号规划到车时间
    ,scan_site_if_stop                     as scan_site_if_stop                    --当前站是否经停
    ,scan_site_actual_shipment_no          as scan_site_actual_shipment_no         --当前站点实际发车任务号
    ,scan_site_actual_shipment_name        as scan_site_actual_shipment_name       --当前站点实际发车任务名
    ,scan_site_arrival_bagg_code           as scan_site_arrival_bagg_code          --当前站点到件包号
    ,scan_site_arrival_bagg_name           as scan_site_arrival_bagg_name          --当前站点到件包名
    ,scan_site_send_bagg_code              as scan_site_send_bagg_code             --当前站点发件包号
    ,scan_site_send_bagg_name              as scan_site_send_bagg_name             --当前站点发件包名
    ,scan_site_send_bagg_addr_code         as scan_site_send_bagg_addr_code        --当前站发件建包目的地编码
    ,scan_site_send_bagg_addr_name         as scan_site_send_bagg_addr_name        --当前站发件建包目的地名称
    ,scan_site_arrival_bagg_addr_code      as scan_site_arrival_bagg_addr_code     --当前站到件建包目的地编码
    ,scan_site_arrival_bagg_addr_name      as scan_site_arrival_bagg_addr_name     --当前站到件建包目的地名称
    ,scan_site_bagg_code                   as scan_site_bagg_code                  --当前站点发件包号
    ,scan_site_bagg_name                   as scan_site_bagg_name                  --当前站点发件包名
    ,next_site_code                        as next_site_code                       --下一站编码
    ,next_site_name                        as next_site_name                       --下一站名称
    ,next_site_type                        as next_site_type                       --下一站类型
    ,next_site_arrival_time                as next_site_arrival_time               --下一站到件时间
    ,next_site_send_time                   as next_site_send_time                  --下一站发件时间
    ,next_site_actual_arrival_time         as next_site_actual_arrival_time        --下一站到车时间
    ,next_site_actual_send_time            as next_site_actual_send_time           --下一站发车时间
    ,next_site_seal_time                   as next_site_seal_time                  --下一站封车时间
    ,next_site_planned_departure_time      as next_site_planned_departure_time     --下一站任务号规划发车时间
    ,next_site_planned_arrival_time        as next_site_planned_arrival_time       --下一站任务号规划到车时间
    ,next_site_if_stop                     as next_site_if_stop                    --下一站是否经停
    ,pre_site_code                         as pre_site_code                        --上一站编码
    ,pre_site_name                         as pre_site_name                        --上一站名称
    ,pre_site_type                         as pre_site_type                        --上一站类型
    ,pre_site_arrival_time                 as pre_site_arrival_time                --上一站到件时间
    ,pre_site_send_time                    as pre_site_send_time                   --上一站发件时间
    ,pre_site_actual_arrival_time          as pre_site_actual_arrival_time         --上一站到车时间
    ,pre_site_actual_send_time             as pre_site_actual_send_time            --上一站发车时间
    ,pre_site_seal_time                    as pre_site_seal_time                   --上一站封车时间
    ,pre_site_planned_departure_time       as pre_site_planned_departure_time      --上一站任务号规划发车时间
    ,pre_site_planned_arrival_time         as pre_site_planned_arrival_time        --上一站任务号规划到车时间
    ,pre_site_if_stop                      as pre_site_if_stop                     --上一站是否经停
    ,pre_l2_site_code                      as pre_l2_site_code                     --上上一站编码
    ,pre_l3_site_code                      as pre_l3_site_code                     --上上上一站编码
    ,pre_l4_site_code                      as pre_l4_site_code                     --上上上上一站编码
    ,first_center_actual_arrival_time      as first_center_actual_arrival_time     --始发中心实际到车时间
    ,first_center_actual_send_time         as first_center_actual_send_time        --始发中心实际发车时间
    ,first_network_code                    as first_network_code                   --始发网点编码
    ,first_network_name                    as first_network_name                   --始发网点名称
    ,null                                  as sign_network_code                    --签收网点编码
    ,null                                  as sign_network_name                    --签收网点名称
    ,last_network_code                     as last_network_code                    --目的网点编码
    ,last_network_name                     as last_network_name                    --目的网点名称
    ,concat(cast((unix_timestamp(case when next_site_if_stop = 1 then next_stop_site_actual_arrival_time --下一站经停点实际到车时间
                                      when scan_site_if_stop = 1 then next_site_stop_actual_arrival_time --经停点下一站实际到车时间
                                      else next_site_arrival_time end) -
                  unix_timestamp(case when scan_site_if_stop = 1 then scan_site_actual_send_time --当前站点实际发车时间
                                      else scan_site_send_time end)) /60 /60 as decimal(15,2)),'H')  as actual_diff_time --当前站点与下一站实际时间差(小时)'
    ,scan_site_simple_name                 as actual_node                          --实际节点
    ,first_code                            as first_code                           --一段码
    ,lasted_center_code                    as lasted_center_code                   --一段码目的中心编码
    ,second_code                           as second_code                          --二段码
    ,lasted_network_code                   as lasted_network_code                  --二段码目的网点
    ,scan_site_if_bagg                     as scan_site_if_bagg                    --是否建包扫描
    ,null as first_duty_type --七星潭平台静态环节责任责 ,
    ,null as first_ziyou_duty_type --自有平台静态环节责任责
     --网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点
    ,case when scan_site_if_stop = 1 then 0 --经停点场景直接置为0
          when (pre_site_type is null or pre_site_type in(5,6)) and scan_site_type = 6 and (next_site_type = 5 or next_l2_site_type = 5 or next_l3_site_type = 6 ) then 1 --网点-集散>网点-网点-集散>网点-集散-网点
          when (pre_site_type is null or pre_site_type in(5,6)) and scan_site_type = 6 and (next_site_type = 4 or next_l2_site_type = 4 or next_l3_site_type = 6 ) then 2 --网点-中心>网点-网点-中心>网点-*-中心
          when pre_site_type in (5,6) and scan_site_type = 5 and next_site_type in (4,5) then 3 --网点/集散-集散-集散/中心
          when pre_site_type in (5,6) and scan_site_type = 4 and next_site_type = 4 then 4  --网点/集散-中心-中心  --出港
          when pre_site_type = 4 and scan_site_type = 4 and next_site_type = 4 then 5 --中心-中心-中心 --出港(中转)
          when pre_site_type in (4,5,6) and scan_site_type = 4 and next_site_type in (5,6) then 6 --中心-中心-集散/网点  --进港
          when pre_site_type = 4 and scan_site_type = 5 and next_site_type in (5,6) then 7 --中心-集散-集散/网点  --散货
          when pre_site_type in (4,5) and scan_site_type = 6 and next_site_type is null  then 8 --集散/中心-网点
          when pre_site_type is null and scan_site_type = 6 and next_site_code is null then 11 --始发网点是最新轨迹
          when pre_site_type in (5,6) and scan_site_type = 5 and next_site_code is null then 13 --始发集散是最新轨迹
          when pre_site_type in (5,6) and scan_site_type = 4 and next_site_code is null and scan_site_code <> last_center_code then 14 --始发中心是最新轨迹
          when pre_site_type = 4 and scan_site_type = 4 and scan_site_code <> last_center_code and next_site_code is null then 15 --中转中心是最新轨迹
          when scan_site_if_stop = 1 and scan_site_type = 4 and next_site_stop_code is null then 16  --经停点是最新轨迹
          when scan_site_type = 4 and scan_site_code = last_center_code and next_site_code is null then 17 --目的中心是最新轨迹
          when pre_site_type = 4 and scan_site_type = 5 and pre_site_code = last_center_code and next_site_type is null then 18  --目的集散是最新轨迹
          else 0 end as type
    /**,case when scan_site_if_stop = 1 then 0 --经停点场景直接置为0
          when (pre_site_type is null or pre_site_type = 6) and scan_site_type = 6 and next_site_type = 5 then 1 --空-网点-集散-
          when (pre_site_type is null or pre_site_type = 6) and scan_site_type = 6 and next_site_type = 4 then 2 --空-网点-中心
          when pre_site_type in (5,6) and scan_site_type = 5 and next_site_type in (4,5) then 3 --网点/集散-集散-集散/中心
          when pre_site_type in (5,6) and scan_site_type = 4 and next_site_type = 4 then 4  --网点/集散-中心-中心  --出港
          when pre_site_type = 4 and scan_site_type = 4 and next_site_type = 4 then 5 --中心-中心-中心 --出港(中转)
          when pre_site_type in (4,5) and scan_site_type = 4 and next_site_type in (5,6) then 6 --中心-中心-集散/网点  --进港
          when pre_site_type = 4 and scan_site_type = 5 and next_site_type in (5,6) then 7 --中心-集散-集散/网点  --散货
          when pre_site_type in (4,5) and scan_site_type = 6 and next_site_type is null  then 8 --集散/中心-网点
          when pre_site_type is null and scan_site_type = 6 and next_site_code is null then 11 --始发网点是最新轨迹
          when pre_site_type in (5,6) and scan_site_type = 5 and next_site_code is null then 13 --始发集散是最新轨迹
          when pre_site_type in (5,6) and scan_site_type = 4 and next_site_code is null and scan_site_code <> last_center_code then 14 --始发中心是最新轨迹
          when pre_site_type = 4 and scan_site_type = 4 and scan_site_code <> last_center_code and next_site_code is null then 15 --中转中心是最新轨迹
          when scan_site_if_stop = 1 and scan_site_type = 4 and next_site_stop_code is null then 16  --经停点是最新轨迹
          when scan_site_type = 4 and scan_site_code = last_center_code and next_site_code is null then 17 --目的中心是最新轨迹
          when pre_site_type = 4 and scan_site_type = 5 and pre_site_code = last_center_code and next_site_type is null then 18  --目的集散是最新轨迹
          else 0 end as type      **/
    ,actual_effect                         as actual_effect                         --动态时效
    ,pre_site_stop_actual_send_time        as pre_site_stop_actual_send_time        --经停站点上一站实际发车时间_20230227新增
    ,pre_site_stop_planned_departure_time  as pre_site_stop_planned_departure_time  --经停站点上一站规划发车时间_20230227新增
    ,next_site_stop_code                   as next_site_stop_code                   --经停站点下一站点编码_20230227新增
    ,next_site_stop_name                   as next_site_stop_name                   --经停站点下一站点名称_20230227新增
    ,next_site_stop_type                   as next_site_stop_type                   --经停站点下一站点类型_20230227新增
    ,next_site_stop_actual_arrival_time    as next_site_stop_actual_arrival_time    --经停站点下一站点实际到车时间_20230227新增
    ,next_site_stop_planned_arrival_time   as next_site_stop_planned_arrival_time   --经停站点下一站点规划到车时间_20230227新增
    ,next_stop_site_actual_send_time       as next_stop_site_actual_send_time       --下一经停站点规划发车时间_20230227新增
    ,next_stop_site_planned_departure_time as next_stop_site_planned_departure_time --下一经停站点规划到车时间_20230227新增
    ,next_stop_site_actual_arrival_time    as next_stop_site_actual_arrival_time    --下一经停站点实际到车时间_20230227新增
    ,next_stop_site_planned_arrival_time   as next_stop_site_planned_arrival_time   --下一经停站点实际发车时间_20230227新增
    ,next_l2_site_code                     as next_l2_site_code                     --下下一站编码
    ,if_taitc                              as if_taitc                              --问题件抬头错标识
    ,order_source_code                     as order_source_code                     --订单来源编码
    ,order_source_name                     as order_source_name                     --订单来源名称
    ,if_reback_reach                       as if_reback_reach                       --转件是否达成
    ,reback_apply_time                     as reback_apply_time                     --退转件申请时间
    ,intercept_registration_time           as intercept_registration_time           --拦截件登记时间
    ,cainiao_plan_sign_time                as cainiao_plan_sign_time                --达成率菜鸟规划签收时间
    ,ziyou_plan_sign_time                  as ziyou_plan_sign_time                  --达成率自有规划签收时间
    ,start_center_code --始发中心编码
    ,start_center_name --始发中心名称
    ,end_center_code   --目的中心编码
    ,end_center_name   --目的中心名称
    ,customer_code             --客户编码
    ,customer_name             --客户名称
    ,scan_site_bagg_user_code  --当前站点建包扫描员编号
    ,scan_site_bagg_user_name  --当前站点建包扫描员名称
    ,pre_site_stop_code        --经停站点上一站编码
    ,pre_site_stop_name        --经停站点上一站名称
    ,pre_site_stop_type        --经停站点上一站类型
    ,actual_sign_code          --时效签收网点
    ,extra_time                --特殊乡镇时间 20230920新增
    ,scan_site_ship_table_type --当前站点发件任务号任务类型 20231009新增
    ,scan_site_ship_next_network_code   --发件任务号任务下一站点编码(用来判断实际场景错分,实际场景中错分了不会发往中心)20231211新增
    ,scan_site_arrival_shipment_no      --当前站点到件任务号20231211新增
    ,pre_site_send_shipment_no          --上一站点发件任务号20231211新增
    ,scan_site_ship_nodes               --当前站点发件任务务号单节点数20231214新增
    ,if_trans_mail --是否转邮件20240117新增
    ,scan_site_min_time --当前站点最早扫描时间
    ,to_date(cainiao_plan_sign_time) as dt --分区
from (
    select
         *
        ,lag( if(scan_site_if_stop <> 1,scan_site_code                  ,null)) over(partition by waybill_no order by scan_site_no) as pre_site_code                    --上一站编码
        ,lag( if(scan_site_if_stop <> 1,scan_site_name                  ,null)) over(partition by waybill_no order by scan_site_no) as pre_site_name                    --上一站名称
        ,lag( if(scan_site_if_stop <> 1,scan_site_type                  ,null)) over(partition by waybill_no order by scan_site_no) as pre_site_type                    --上一站类型
        ,lag( if(scan_site_if_stop <> 1,scan_site_arrival_time          ,null)) over(partition by waybill_no order by scan_site_no) as pre_site_arrival_time            --上一站到件时间
        ,lag( if(scan_site_if_stop <> 1,scan_site_send_time             ,null)) over(partition by waybill_no order by scan_site_no) as pre_site_send_time               --上一站发件时间
        ,lag( if(scan_site_if_stop <> 1,scan_site_actual_arrival_time   ,null)) over(partition by waybill_no order by scan_site_no) as pre_site_actual_arrival_time     --上一站到车时间
        ,lag( if(scan_site_if_stop <> 1,scan_site_actual_send_time      ,null)) over(partition by waybill_no order by scan_site_no) as pre_site_actual_send_time        --上一站发车时间
        ,lag( if(scan_site_if_stop <> 1,scan_site_seal_time             ,null)) over(partition by waybill_no order by scan_site_no) as pre_site_seal_time               --上一站封车时间
        ,lag( if(scan_site_if_stop <> 1,scan_site_planned_departure_time,null)) over(partition by waybill_no order by scan_site_no) as pre_site_planned_departure_time  --上一站任务号规划发车时间
        ,lag( if(scan_site_if_stop <> 1,scan_site_planned_arrival_time  ,null)) over(partition by waybill_no order by scan_site_no) as pre_site_planned_arrival_time    --上一站任务号规划到车时间
        ,lag( if(scan_site_if_stop <> 1,scan_site_if_stop               ,null)) over(partition by waybill_no order by scan_site_no) as pre_site_if_stop                 --上一站是否经停
        ,lead(if(scan_site_if_stop <> 1,scan_site_code                  ,null)) over(partition by waybill_no order by scan_site_no) as next_site_code                   --下一站编码
        ,lead(if(scan_site_if_stop <> 1,scan_site_name                  ,null)) over(partition by waybill_no order by scan_site_no) as next_site_name                   --下一站名称
        ,lead(if(scan_site_if_stop <> 1,scan_site_type                  ,null)) over(partition by waybill_no order by scan_site_no) as next_site_type                   --下一站类型
        ,lead(if(scan_site_if_stop <> 1,scan_site_arrival_time          ,null)) over(partition by waybill_no order by scan_site_no) as next_site_arrival_time           --下一站到件时间
        ,lead(if(scan_site_if_stop <> 1,scan_site_send_time             ,null)) over(partition by waybill_no order by scan_site_no) as next_site_send_time              --下一站发件时间
        ,lead(if(scan_site_if_stop <> 1,scan_site_actual_arrival_time   ,null)) over(partition by waybill_no order by scan_site_no) as next_site_actual_arrival_time    --下一站到车时间
        ,lead(if(scan_site_if_stop <> 1,scan_site_actual_send_time      ,null)) over(partition by waybill_no order by scan_site_no) as next_site_actual_send_time       --下一站发车时间
        ,lead(if(scan_site_if_stop <> 1,scan_site_seal_time             ,null)) over(partition by waybill_no order by scan_site_no) as next_site_seal_time              --下一站封车时间
        ,lead(if(scan_site_if_stop <> 1,scan_site_planned_departure_time,null)) over(partition by waybill_no order by scan_site_no) as next_site_planned_departure_time --下一站任务号规划发车时间
        ,lead(if(scan_site_if_stop <> 1,scan_site_planned_arrival_time  ,null)) over(partition by waybill_no order by scan_site_no) as next_site_planned_arrival_time   --下一站任务号规划到车时间
        ,lag( if(scan_site_if_stop <> 1,scan_site_code,null)            ,2    ) over(partition by waybill_no order by scan_site_no) as pre_l2_site_code                 --上上一站编码
        ,lag( if(scan_site_if_stop <> 1,scan_site_code,null)            ,3    ) over(partition by waybill_no order by scan_site_no) as pre_l3_site_code                 --上上上一站编码
        ,lag( if(scan_site_if_stop <> 1,scan_site_code,null)            ,4    ) over(partition by waybill_no order by scan_site_no) as pre_l4_site_code                 --上上上上一站编码
        ,lead(if(scan_site_if_stop <> 1,scan_site_code,null)            ,2    ) over(partition by waybill_no order by scan_site_no) as next_l2_site_code                --下下一站编码
        ,lead(if(scan_site_if_stop <> 1,scan_site_type,null)            ,2    ) over(partition by waybill_no order by scan_site_no) as next_l2_site_type                --下下一站类型
        ,lead(if(scan_site_if_stop <> 1,scan_site_type,null)            ,3    ) over(partition by waybill_no order by scan_site_no) as next_l3_site_type                --下下下一站类型
        ,max(first_center_actual_arrival_time_tmp) over(partition by waybill_no ) as first_center_actual_arrival_time --始发中心到车时间
        ,max(first_center_actual_send_time_tmp   ) over(partition by waybill_no ) as first_center_actual_send_time    --始发中心发车时间
        ,if(actual_sign_time_tmp <>'2099-12-31 00:00:00',actual_sign_time_tmp,null) as actual_sign_time       --实际签收时间
        ,if(aging_sign_time_tmp  <>'2099-12-31 00:00:00',aging_sign_time_tmp ,null) as aging_sign_time        --时效签收时间
        ,case when scan_site_if_stop = 1 then lag(scan_site_code                   ) over(partition by waybill_no order by nvl(scan_site_actual_arrival_time,scan_site_actual_send_time)) end  as pre_site_stop_code --经停站点上一站编码_20230824新增
        ,case when scan_site_if_stop = 1 then lag(scan_site_name                   ) over(partition by waybill_no order by nvl(scan_site_actual_arrival_time,scan_site_actual_send_time)) end  as pre_site_stop_name --经停站点上一站名称_20230824新增
        ,case when scan_site_if_stop = 1 then lag(scan_site_type                   ) over(partition by waybill_no order by nvl(scan_site_actual_arrival_time,scan_site_actual_send_time)) end  as pre_site_stop_type --经停站点上一站类型_20230824新增
        ,case when scan_site_if_stop = 1 then lag(scan_site_actual_send_time       ) over(partition by waybill_no order by nvl(scan_site_actual_arrival_time,scan_site_actual_send_time)) end  as pre_site_stop_actual_send_time       --经停站点上一站实际发车时间_20230227新增
        ,case when scan_site_if_stop = 1 then lag(scan_site_planned_departure_time ) over(partition by waybill_no order by nvl(scan_site_actual_arrival_time,scan_site_actual_send_time)) end  as pre_site_stop_planned_departure_time --经停站点上一站规划发车时间_20230227新增
        ,case when scan_site_if_stop = 1 then lead(scan_site_code                  ) over(partition by waybill_no order by nvl(scan_site_actual_arrival_time,scan_site_actual_send_time)) end  as next_site_stop_code                  --经停站点下一站点编码_20230227新增
        ,case when scan_site_if_stop = 1 then lead(scan_site_name                  ) over(partition by waybill_no order by nvl(scan_site_actual_arrival_time,scan_site_actual_send_time)) end  as next_site_stop_name                  --经停站点下一站点名称_20230227新增
        ,case when scan_site_if_stop = 1 then lead(scan_site_type                  ) over(partition by waybill_no order by nvl(scan_site_actual_arrival_time,scan_site_actual_send_time)) end  as next_site_stop_type                  --经停站点下一站点名称_20230227新增
        ,case when scan_site_if_stop = 1 then lead(scan_site_actual_arrival_time   ) over(partition by waybill_no order by nvl(scan_site_actual_arrival_time,scan_site_actual_send_time)) end  as next_site_stop_actual_arrival_time   --经停站点下一站点实际到车时间_20230227新增
        ,case when scan_site_if_stop = 1 then lead(scan_site_planned_arrival_time  ) over(partition by waybill_no order by nvl(scan_site_actual_arrival_time,scan_site_actual_send_time)) end  as next_site_stop_planned_arrival_time  --经停站点下一站点计划到车时间_20230227新增
        ,lead(case when scan_site_if_stop = 1 then scan_site_if_stop                end) over(partition by waybill_no order by nvl(scan_site_actual_arrival_time,scan_site_actual_send_time)) as next_site_if_stop                     --下一站点是否经停_20230227新增
        ,lead(case when scan_site_if_stop = 1 then scan_site_code                   end) over(partition by waybill_no order by nvl(scan_site_actual_arrival_time,scan_site_actual_send_time)) as next_stop_site_code                   --下一站点是否经停_20230227新增
        ,lead(case when scan_site_if_stop = 1 then scan_site_actual_send_time       end) over(partition by waybill_no order by nvl(scan_site_actual_arrival_time,scan_site_actual_send_time)) as next_stop_site_actual_send_time       --下一经停站点规划发车时间_20230227新增
        ,lead(case when scan_site_if_stop = 1 then scan_site_planned_departure_time end) over(partition by waybill_no order by nvl(scan_site_actual_arrival_time,scan_site_actual_send_time)) as next_stop_site_planned_departure_time --下一经停站点规划到车时间_20230227新增
        ,lead(case when scan_site_if_stop = 1 then scan_site_actual_arrival_time    end) over(partition by waybill_no order by nvl(scan_site_actual_arrival_time,scan_site_actual_send_time)) as next_stop_site_actual_arrival_time    --下一经停站点实际到车时间_20230227新增
        ,lead(case when scan_site_if_stop = 1 then scan_site_planned_arrival_time   end) over(partition by waybill_no order by nvl(scan_site_actual_arrival_time,scan_site_actual_send_time)) as next_stop_site_planned_arrival_time   --下一经停站点实际发车时间_20230227新增
    from (
        select
             step.waybill_no                                            as waybill_no                                  --运单号
            ,step.scan_site_no                                          as scan_site_no                                --当前站点顺序号
            ,step.scan_site_code                                        as scan_site_code                              --当前站点编码
            ,step.scan_site_name                                        as scan_site_name                              --当前站点名称
            ,dim.network_type                                           as scan_site_type                              --当前站点类型
            ,step.scan_site_arrival_time                                as scan_site_arrival_time                      --当前站点到件时间
            ,step.scan_site_send_time                                   as scan_site_send_time                         --当前站点发件时间
            ,step.scan_site_seal_time                                   as scan_site_seal_time                         --当前站点封车时间
            ,case when step.scan_site_arrival_ship_table_type = 1 then --如果到车任务单是干线任务号那就用发车任务单的时间
                  case when step.pre_site_send_shipment_no is not null then --上一站有发件任务号
                       case when step.pre_site_send_ship_attr = 1 and (step.scan_site_actual_arrival_time is not null or step.scan_site_planned_arrival_time is not null) then --上一站发件任务号是加班车
                            greatest(nvl(step.scan_site_actual_arrival_time,step.scan_site_arrival_time),nvl(step.scan_site_planned_arrival_time,'0000-00-00 00:00:00'))
                            else nvl(step.scan_site_actual_arrival_time,step.scan_site_arrival_time)
                          end
                       else
                            case when step.scan_site_arrival_ship_attr = 1 and (step.scan_site_arrival_actual_arrival_time is not null or step.scan_site_arrival_planned_arrival_time is not null) then --当前到件任务号是正班车
                                 greatest(nvl(step.scan_site_arrival_actual_arrival_time,step.scan_site_arrival_time),nvl(step.scan_site_arrival_planned_arrival_time,'0000-00-00 00:00:00'))
                                 else nvl(step.scan_site_arrival_actual_arrival_time,step.scan_site_arrival_time)
                            end
                       end
                  when step.scan_site_arrival_ship_table_type = 2 then --如果到车任务单是支线任务号那就用到车任务单的时间
                       nvl(step.scan_site_arrival_actual_arrival_time,step.scan_site_arrival_time)

                  when step.scan_site_arrival_ship_table_type is null then --当前到件漏扫
                  case when pre_site_send_ship_table_type = 1 and step.pre_site_send_ship_attr = 1 then --上一站是干线,且是正班,做对比
                       greatest(nvl(step.scan_site_actual_arrival_time,step.scan_site_arrival_time),nvl(step.scan_site_planned_arrival_time,'0000-00-00 00:00:00'))
                       when pre_site_send_ship_table_type = 1 and step.pre_site_send_ship_attr = 2 then --上一站是干线,且是加班,不做对比
                       nvl(step.scan_site_actual_arrival_time,step.scan_site_arrival_time)
                       else nvl(step.scan_site_actual_arrival_time,step.scan_site_arrival_time) --其他都是支线,优先级判断
                   end
                  else nvl(step.scan_site_actual_arrival_time,step.scan_site_arrival_time) --其他场景都用上一站
                  end as scan_site_actual_arrival_time --当前站点实际到车时间
            ,case when step.scan_site_actual_shipment_no is not null and step.scan_site_ship_attr = 1
                  and (step.scan_site_actual_send_time is not null or step.scan_site_planned_departure_time is not null) then
                  greatest(nvl(step.scan_site_actual_send_time,step.scan_site_send_time),nvl(step.scan_site_planned_departure_time,'0000-00-00 00:00:00'))
                  else nvl(step.scan_site_actual_send_time,step.scan_site_send_time)
                  end as scan_site_actual_send_time --当前站点实际发车时间
            ,step.scan_site_planned_departure_time                      as scan_site_planned_departure_time            --当前站点到件任务号规划发车时间
            ,step.scan_site_planned_arrival_time                        as scan_site_planned_arrival_time              --当前站点到件任务号规划到车时间
            ,step.scan_site_actual_shipment_no                          as scan_site_actual_shipment_no                --当前站点实际发车任务号
            ,step.scan_site_actual_shipment_name                        as scan_site_actual_shipment_name              --当前站点实际发车任务名
            ,step.scan_site_arrival_bagg_code                           as scan_site_arrival_bagg_code                 --当前站点到件包号
            ,step.scan_site_arrival_bagg_name                           as scan_site_arrival_bagg_name                 --当前站点到件包名
            ,step.scan_site_send_bagg_code                              as scan_site_send_bagg_code                    --当前站点发件包号
            ,step.scan_site_send_bagg_name                              as scan_site_send_bagg_name                    --当前站点发件包名
            ,step.scan_site_send_bagg_addr_code                         as scan_site_send_bagg_addr_code               --当前站发件建包目的地编码
            ,step.scan_site_send_bagg_addr_name                         as scan_site_send_bagg_addr_name               --当前站发件建包目的地名称
            ,step.scan_site_arrival_bagg_addr_code                      as scan_site_arrival_bagg_addr_code            --当前站到件建包目的地编码
            ,step.scan_site_arrival_bagg_addr_name                      as scan_site_arrival_bagg_addr_name            --当前站到件建包目的地名称
            ,nvl(step.scan_site_arrival_bagg_code,step.scan_site_send_bagg_code) as scan_site_bagg_code                --当前站点发件包号
            ,nvl(step.scan_site_arrival_bagg_name,step.scan_site_send_bagg_name) as scan_site_bagg_name                --当前站点发件包名
            ,step.deliver_time                                          as deliver_time                                --目的网点实际出仓时间
            ,step.last_network_code                                     as last_network_code                           --目的网点编码
            ,step.last_network_name                                     as last_network_name                           --目的网点名称
            ,dim.simple_name                                            as scan_site_simple_name                       --当前站点简称
            ,step.first_code                                            as first_code                                  --一段码
            ,nvl(first.lasted_center_code,pack_diff.center_code)        as lasted_center_code                          --一段码目的中心编码
            ,nvl(first.lasted_center_name,pack_diff.center_name)        as lasted_center_name                          --一段码目的中心名称
            ,first.last_center_code                                     as last_center_code                            --建包目的地
            ,first.last_center_name                                     as last_center_name                            --建包目的地
            ,step.second_code                                           as second_code                                 --二段码
            ,second.lasted_network_code                                   as lasted_network_code                         --二段码目的网点编码
            ,second.lasted_network_name                                   as lasted_network_name                         --二段码目的网点名称
            ,step.scan_site_if_stop                                     as scan_site_if_stop                           --是否经停
            ,step.actual_sign_time_tmp                                  as actual_sign_time_tmp                        --实际签收时间
            ,step.aging_sign_time_tmp                                   as aging_sign_time_tmp                         --实效签收时间
            ,step.scan_site_if_bagg                                     as scan_site_if_bagg                           --当前站点是否建包扫描
            ,case when step.scan_site_center_no = 1 then step.scan_site_code end as first_center_code --始发中心编码
            ,case when step.scan_site_center_no = 1 then step.scan_site_name end as first_center_name --始发中心名称
            ,case when step.scan_site_center_no = 1 then
                  case when step.scan_site_arrival_ship_table_type = 1 then --如果到车任务单是干线任务号那就用发车任务单的时间
                       case when step.pre_site_send_shipment_no is not null then --上一站有发件任务号
                            case when step.pre_site_send_ship_attr = 1 and (step.scan_site_actual_arrival_time is not null or step.scan_site_planned_arrival_time is not null) then --上一站发件任务号是加班车
                                 greatest(nvl(step.scan_site_actual_arrival_time,step.scan_site_arrival_time),nvl(step.scan_site_planned_arrival_time,'0000-00-00 00:00:00'))
                                 else nvl(step.scan_site_actual_arrival_time,step.scan_site_arrival_time)
                               end
                            else
                                 case when step.scan_site_arrival_ship_attr = 1 and (step.scan_site_arrival_actual_arrival_time is not null or step.scan_site_arrival_planned_arrival_time is not null) then --当前到件任务号是正班车
                                      greatest(nvl(step.scan_site_arrival_actual_arrival_time,step.scan_site_arrival_time),nvl(step.scan_site_arrival_planned_arrival_time,'0000-00-00 00:00:00'))
                                      else nvl(step.scan_site_arrival_actual_arrival_time,step.scan_site_arrival_time)
                                 end
                            end
                       when step.scan_site_arrival_ship_table_type = 2 then --如果到车任务单是支线任务号那就用到车任务单的时间
                            nvl(step.scan_site_arrival_actual_arrival_time,step.scan_site_arrival_time)

                       when step.scan_site_arrival_ship_table_type is null then --当前到件漏扫
                       case when pre_site_send_ship_table_type = 1 and step.pre_site_send_ship_attr = 1 then --上一站是干线,且是正班,做对比
                            greatest(nvl(step.scan_site_actual_arrival_time,step.scan_site_arrival_time),nvl(step.scan_site_planned_arrival_time,'0000-00-00 00:00:00'))
                            when pre_site_send_ship_table_type = 1 and step.pre_site_send_ship_attr = 2 then --上一站是干线,且是加班,不做对比
                            nvl(step.scan_site_actual_arrival_time,step.scan_site_arrival_time)
                            else nvl(step.scan_site_actual_arrival_time,step.scan_site_arrival_time) --其他都是支线,优先级判断
                        end
                       else nvl(step.scan_site_actual_arrival_time,step.scan_site_arrival_time) --其他场景都用上一站
                      end
                  end as first_center_actual_arrival_time_tmp      --始发中心到车时间

            ,case when step.scan_site_center_no = 1 then
                  case when step.scan_site_actual_shipment_no is not null and step.scan_site_ship_attr = 1
                      and (step.scan_site_actual_send_time is not null or step.scan_site_planned_departure_time is not null) then
                      greatest(nvl(step.scan_site_actual_send_time,step.scan_site_send_time),nvl(step.scan_site_planned_departure_time,'0000-00-00 00:00:00'))
                      else nvl(step.scan_site_actual_send_time,step.scan_site_send_time)
                  end
              end as first_center_actual_send_time_tmp --始发中心发车时间
            ,step.ziyou_platform_effect              --自有平台时效标准（区县时效）
            ,step.if_platform_standard               --自有平台是否达成
            ,step.cainiao_platform_name              --七星潭平台名称
            ,step.cainiao_platform_effect            --七星潭平台时效标准
            ,step.if_cainiao_platform_pres_standard  --七星潭平台时效是否达成:1达成,0未达成
            ,step.start_city_code                    --始发城市编码
            ,step.start_city_name                    --始发城市名称
            ,step.sign_city_code                     --签收城市编码
            ,step.sign_city_name                     --签收城市名称
            ,step.taking_time                        --揽收时间
            ,step.taking_date                        --揽收日期
            ,step.first_network_code                 --始发网点编码
            ,step.first_network_name                 --始发网点名称
            ,step.plan_sign_date                     --规划签收时间
            ,step.economic_circle_name               --经济圈名称
            ,step.if_reback                          --是否退回件:1是,0否
            ,step.if_transfer                        --是否转寄件:1是,0否
            ,step.if_intercept                       --是否拦截件:1是,0否
            ,step.actual_effect                      --实际时效  20230216新增
            ,step.if_taitc                           --问题件抬头错标识
            ,step.order_source_code                  --订单来源编码
            ,step.order_source_name                  --订单来源名称
            ,step.if_reback_reach                    --判责自有平台是否达成
            ,step.reback_apply_time                  --退转件申请时间
            ,step.intercept_registration_time        --拦截件登记时间
            ,step.cainiao_plan_sign_time             --达成率菜鸟规划签收时间
            ,step.ziyou_plan_sign_time               --达成率自有规划签收时间
            ,step.start_center_code                  --始发转运中心编码
            ,step.start_center_name                  --始发转运中心名称
            ,step.end_center_code                    --目的转运中心编码
            ,step.end_center_name                    --目的转运中心名称
            ,step.actual_sign_code                   --时效签收网点
            ,step.customer_code                      --客户编码
            ,step.customer_name                      --客户名称
            ,step.scan_site_bagg_user_code           --当前站点建包扫描员编号
            ,step.scan_site_bagg_user_name           --当前站点建包扫描员名称
            ,step.extra_time                         --特殊乡镇时间 20230920新增
            ,step.scan_site_ship_table_type          --发件任务号任务类型1-干线；2-支线(用来给直发场景判责)20231009新增
            ,step.scan_site_ship_next_network_code   --发件任务号任务下一站点编码(用来判断实际场景错分,实际场景中错分了不会发往中心)20231211新增
            ,step.pre_site_send_shipment_no          --上一站发件任务号20231211新增
            ,step.scan_site_arrival_shipment_no      --当前站到件任务号20231211新增
            ,step.scan_site_ship_nodes               --发件任务号任务单节点数20231214新增
            ,step.if_trans_mail                      --是否转邮件 
            ,step.scan_site_min_time                 --每个站点最早扫描时间
            ,min(case when second.lasted_network_code = step.scan_site_code then step.scan_site_no end) over(partition by waybill_no)  as lasted_network_no --二段码目的网点的节点数
        from (
            select *
            from dm_waybill_step_reach_details_dt step
            union all
            select
                 step.waybill_no
                ,null as scan_site_no                        --节点顺序
                ,ship.network_code as scan_site_code         --当前站点编码
                ,ship.network_name as scan_site_name         --当前站点名称
                ,null as scan_site_center_no                 --当前站点中心编号
                ,step.deliver_time                           --目的网点实际出仓时间
                ,step.last_network_code                      --目的网点编码
                ,step.last_network_name                      --目的网点名称
                ,step.first_code                             --一段码
                ,step.second_code                            --二段码
                ,step.actual_sign_time_tmp                   --实际签收时间
                ,step.aging_sign_time_tmp                    --实效签收时间
                ,null as scan_site_arrival_time              --到件时间
                ,null as scan_site_send_time                 --发件时间
                ,step.scan_site_actual_shipment_no           --发件任务号
                ,step.scan_site_actual_shipment_name         --发件任务号名称
                ,step.scan_site_ship_attr                as scan_site_ship_attr                 --发件任务号业务属性1:正班2:加班
                ,step.scan_site_ship_table_type          as scan_site_ship_table_type           --发件任务号任务类型1-干线；2-支线
                ,step.scan_site_ship_next_network_code   as scan_site_ship_next_network_code    --发件任务号任务下一站点编码
                ,step.scan_site_ship_nodes               as scan_site_ship_nodes                --发件任务号任务单节点数
                ,ship.lock_time                          as scan_site_seal_time                 --发件任务单实际封车时间
                ,ship.actual_departure_time              as scan_site_actual_send_time          --发件任务号实际发车时间
                ,ship.planned_departure_time             as scan_site_planned_departure_time    --发件任务号规划发车时间
                ,ship.actual_arrival_time                as scan_site_actual_arrival_time       --发件任务号实际到达时间
                ,ship.planned_arrival_time               as scan_site_planned_arrival_time      --发件任务号规划到达时间
                ,null                                    as scan_site_arrival_bagg_code         --到件包号
                ,null                                    as scan_site_arrival_bagg_name         --到件包名
                ,null                                    as scan_site_arrival_bagg_addr_code    --到件包建包目的地编码
                ,null                                    as scan_site_arrival_bagg_addr_name    --到件包建包目的地名称
                ,null                                    as scan_site_send_bagg_code            --发件包号
                ,null                                    as scan_site_send_bagg_name            --发件包名
                ,null                                    as scan_site_send_bagg_addr_code       --发件包建包目的地编码
                ,null                                    as scan_site_send_bagg_addr_name       --发件包建包目的地名称
                ,ship.is_transfer                        as scan_site_if_stop                   --发件是否经停
                ,step.ziyou_platform_effect              as ziyou_platform_effect               --自有平台时效标准（区县时效）
                ,step.if_platform_standard               as if_platform_standard                --自有平台是否达成
                ,step.cainiao_platform_name              as cainiao_platform_name               --七星潭平台名称
                ,step.cainiao_platform_effect            as cainiao_platform_effect             --七星潭平台时效标准
                ,step.if_cainiao_platform_pres_standard  as if_cainiao_platform_pres_standard   --七星潭平台时效是否达成:1达成,0未达成
                ,step.start_city_code                    as start_city_code                     --始发城市编码
                ,step.start_city_name                    as start_city_name                     --始发城市名称
                ,step.sign_city_code                     as sign_city_code                      --签收城市编码
                ,step.sign_city_name                     as sign_city_name                      --签收城市名称
                ,step.taking_time                        as taking_time                         --揽收时间
                ,step.taking_date                        as taking_date                         --揽收日期
                ,step.first_network_code                 as first_network_code                  --始发网点编码
                ,step.first_network_name                 as first_network_name                  --始发网点名称
                ,step.plan_sign_date                     as plan_sign_date                      --规划签收时间
                ,step.economic_circle_name               as economic_circle_name                --经济圈名称
                ,step.if_reback                          as if_reback                           --是否退回件:1是,0否
                ,step.if_transfer                        as if_transfer                         --是否转寄件:1是,0否
                ,step.if_intercept                       as if_intercept                        --是否拦截件:1是,0否
                ,step.actual_effect                      as actual_effect                       --实际时效
                ,step.if_taitc                           as if_taitc                            --问题件抬头错标识
                ,step.order_source_code                  as order_source_code                   --订单来源编码
                ,step.order_source_name                  as order_source_name                   --订单来源名称
                ,step.if_reback_reach                    as if_reback_reach                     --是否退转件达成
                ,step.reback_apply_time                  as reback_apply_time                   --退转件申请时间
                ,step.intercept_registration_time        as intercept_registration_time         --拦截件登记时间
                ,step.cainiao_plan_sign_time             as cainiao_plan_sign_time              --达成率菜鸟规划签收时间
                ,step.ziyou_plan_sign_time               as ziyou_plan_sign_time                --达成率自由规划签收时间
                ,step.start_center_code                  as start_center_code                   --始发转运中心编码
                ,step.start_center_name                  as start_center_name                   --始发转运中心名称
                ,step.end_center_code                    as end_center_code                     --目的转运中心编码
                ,step.end_center_name                    as end_center_name                     --目的转运中心名称
                ,step.scan_site_if_bagg                  as scan_site_if_bagg                   --当前站点是否建包扫描
                ,step.next_site_code_tmp                 as next_site_code_tmp                  --下一站
                ,step.actual_sign_code                   as actual_sign_code                    --时效签收网点
                ,step.scan_site_arrival_shipment_no      as scan_site_arrival_shipment_no       --到件任务号
                ,step.customer_code                      as customer_code                       --客户编码
                ,step.customer_name                      as customer_name                       --客户名称
                ,step.scan_site_bagg_user_code           as scan_site_bagg_user_code            --当前站点建包扫描员编号
                ,step.scan_site_bagg_user_name           as scan_site_bagg_user_name            --当前站点建包扫描员名称
                ,step.extra_time                         as extra_time                          --特殊乡镇时间 20230920新增
                ,null as scan_site_arrival_ship_attr            --到件任务号业务属性1:正班2:加班
                ,null as scan_site_arrival_ship_table_type      --到件任务号任务类型1-干线；2-支线
                ,null as scan_site_arrival_planned_arrival_time --到件任务号计划到车时间
                ,null as scan_site_arrival_actual_arrival_time  --到件任务号实际到车时间
                ,null as pre_site_send_shipment_no              --上一站发件任务号
                ,null as pre_site_send_ship_attr                --上一站发件任务号业务属性1:正班2:加班
                ,null as pre_site_send_ship_table_type          --上一站发件任务号任务类型1-干线；2-支线
                ,step.if_trans_mail as if_trans_mail --是否转邮件 
                ,step.scan_site_min_time as scan_site_min_time --每个站点最早扫描时间
            from dm_waybill_step_reach_details_dt as step
            left join (
                select *
                from jms_dwd.dwd_tms_shipment_stop_point_dt --干线经停点中间表
                where dt = '{{ execution_date | cst_ds }}'
                and ship_type = '干线'
            ) ship on ship.shipment_no = nvl(step.scan_site_actual_shipment_no,step.waybill_no) --优化逻辑为空场景
                  and step.scan_site_code = ship.pre_network_code
            where ship.shipment_no is not null
            and case when step.next_site_code_tmp = ship.network_code then 0 else 1 end = 1
        ) step
        left join jms_dim.dim_network_whole_massage dim on step.scan_site_code = dim.code
        left join (
            select
                 code
                ,center_code as lasted_center_code
                ,center_name as lasted_center_name
                ,dest_center_code as last_center_code
                ,dest_center_name as last_center_name
            from jms_dim.dim_lmdm_sys_first_code --一段码
            where is_enable = 1
            and is_delete = 1
            group by code
                    ,center_code
                    ,center_name
                    ,dest_center_code
                    ,dest_center_name
        ) first on step.first_code = first.code
        left join (
            select
                 code
                ,center_code
                ,center_name
                ,express_center_code
            from (
                 select
                      express_center_short_code  as code
                     ,consolidation_code         as center_code
                     ,consolidation_name         as center_name
                     ,express_center_code
                     ,row_number() over (partition by express_center_short_code order by update_time desc) as rn
                 from jms_dim.dim_yl_sys_package_different_base t --差异化建包
             ) a where a.rn = 1
        ) pack_diff on step.first_code = pack_diff.code
        left join (
            select
                 code
                ,network_code as lasted_network_code 
                ,network_name as lasted_network_name 
                ,center_code
            from jms_dim.dim_mysql_lmdm_sys_second_code --二段码
            where is_enable = 1
            and is_delete = 1
        ) second on second.code = step.second_code
                and second.center_code = nvl(first.last_center_code,pack_diff.express_center_code) --如果一段码没有目的中心，则用差异化建包的目的中心兜底
    ) a where nvl(scan_site_no,0) <= nvl(lasted_network_no,999)
) a 
distribute by dt,abs(hash(waybill_no)) % 120
;